#ifndef SQLITE_DATABASE_H
#define SQLITE_DATABASE_H

#include <sqlite3.h>
#include <string>
#include <vector>
#include <functional>

class SqliteRowData {
public:
    std::string __tableName;
    std::vector<void*> __pointerDatas;
    std::vector<bool> __column_nulls;
    std::vector<int> __types;
    std::vector<std::string> __fieldNames;
    int __columnCount;
    SqliteRowData(const std::string& tableName, int count): __tableName(tableName), __columnCount(count){
        __pointerDatas.resize(count);
        __column_nulls.resize(count);
        __types.resize(count);
        __fieldNames.resize(count);
    }
    inline bool columnIsNull(int column){
        return __column_nulls[column];
    }
    inline void setColumnNull(int column){
        __column_nulls[column] = true;
    }
    std::string getColumnSqlValue(int column){
        // if()
        switch(__types[column]){
        case 0: return std::to_string(*reinterpret_cast<int*>(__pointerDatas[column]));
        case 1: return std::to_string(*reinterpret_cast<double*>(__pointerDatas[column]));
        case 2: {
            const std::string& text = *reinterpret_cast<std::string*>(__pointerDatas[column]);
            size_t index = text.find('\'');
            if(index == text.npos){
                return '\'' + text + '\'';
            }else{
                std::string ntext(text);
                while(index != text.npos){
                    ntext.replace(index, 1, "''");
                    index = text.find('\'', index + 2);
                }
                return '\'' + ntext + '\'';
            }
        }
        default: return "NULL";
        }
    }

public:
    virtual void __setColumnPointer(int column){
        (void)column;
    }
    void __initializeConstructor(){
        int count = static_cast<int>(__pointerDatas.size());
        for(int column = 0; column < count; ++column){
            __setColumnPointer(column);
        }
    }
    template<class Type>
    inline int typeInfo(Type& field){
        if(typeid(field) == typeid(int)){
            return 0;
        }else if(typeid(field) == typeid(double)){
            return 1;
        }else if(typeid(field) == typeid(std::string)){
            return 2;
        }else return -1;
    }

public:
    std::vector<int> __orderByColumns;
    std::vector<bool> __orderByIsAscs;
    bool __hasOrderBy = false;
    void addOrderBy(int column = 0, bool isAsc = true){
        __orderByColumns.push_back(column);
        __orderByIsAscs.push_back(isAsc);
        __hasOrderBy = true;
    }
    std::string __sql_orderBy(){
        std::string result = " ORDER BY ";
        for(size_t column = 0; column < __orderByColumns.size(); ++column){
            if(column) result += ',';
            result += __fieldNames.at(column);
            result += (__orderByIsAscs.at(column) ? " ASC" : " DESC");
        }
        return result;
    }

public:
    std::string sqlSelect(int column){
        std::string sql = __sql_select() + __sql_where(column);
        if(__hasOrderBy){
            sql += __sql_orderBy();
        }
        return sql;
    }
    std::string sqlSelect(const std::vector<int>& wheres = std::vector<int>()){
        std::string sql = __sql_select();
        if(wheres.size()){
            sql += __sql_where(wheres);
        }
        if(__hasOrderBy){
            sql += __sql_orderBy();
        }
        return sql;
    }
    inline std::string sqlInsert(){
        return __sql_insert() + __sql_insert_values();
    }
    std::string sqlUpdate(int set, const std::vector<int>& wheres = std::vector<int>()){
        std::string sql = __sql_update() + __sql_update_set(set);
        if(wheres.size()){
            sql += __sql_where(wheres);
        }
        return sql;
    }
    inline std::string sqlUpdate(int set, int where){
        return __sql_update() + __sql_update_set(set) + __sql_where(where);
    }
    std::string sqlUpdate(const std::vector<int>& sets, int where){
        return __sql_update() + __sql_update_set(sets) + __sql_where(where);
    }
    std::string sqlUpdate(const std::vector<int>& sets, const std::vector<int>& wheres = std::vector<int>()){
        std::string sql = __sql_update() + __sql_update_set(sets);
        if(wheres.size()){
            sql += __sql_where(wheres);
        }
        return sql;
    }
    inline std::string sqlDelete(int column){
        return __sql_delete() + __sql_where(column);
    }
    std::string sqlDelete(const std::vector<int>& wheres = std::vector<int>()){
        std::string sql = __sql_delete();
        if(wheres.size()){
            sql += __sql_where(wheres);
        }
        return sql;
    }

public:
    inline std::string __sql_select(){
        return "SELECT * FROM " + __tableName;
    }
    inline std::string __sql_insert(){
        return "INSERT INTO " + __tableName;
    }
    inline std::string __sql_update(){
        return "UPDATE " + __tableName + " SET ";
    }
    inline std::string __sql_delete(){
        return "DELETE FROM " + __tableName;
    }
    inline std::string __execute_value(int column){
        return __fieldNames.at(column) + '=' + getColumnSqlValue(column);
    }
    std::string __insert_values(){
        std::string result;
        for(int column = 0; column < __columnCount; ++column){
            if(column) result += ',';
            result += __execute_value(column);
        }
        return result;
    }

public:
    std::string __sql_insert_values(const std::vector<int>& columns = std::vector<int>()){
        std::string result;
        if(columns.size()){
            result += '(';
            for(int column : columns){
                if(column) result += ',';
                result += __fieldNames.at(column);
            }
            result += ')';
        }
        result += " VALUES (";
        if(columns.size()){
            for(int column : columns){
                if(column) result += ',';
                result += getColumnSqlValue(column);
            }
        }else{
            for(int column = 0; column < __columnCount; ++column){
                if(column) result += ',';
                result += getColumnSqlValue(column);
            }
        }
        result += ')';
        return result;
    }
    std::string __sql_update_set(int column){
        return __execute_value(column);
    }
    std::string __sql_update_set(const std::vector<int>& columns){
        std::string result;
        for(size_t index = 0; index < columns.size(); ++index){
            if(index) result += ',';
            result += __execute_value(columns.at(index));
        }
        return result;
    }

    std::string __sql_where(int column){
        return " WHERE " + __execute_value(column);
    }
    std::string __sql_where(const std::vector<int>& columns){
        std::string result = " WHERE ";
        for(size_t index = 0; index < columns.size(); ++index){
            if(index) result += " AND ";
            result += __execute_value(columns.at(index));
        }
        return result;
    }
};

/*
class User : public SqliteRowData {
public:
    InitializeSqliteRowData(User, 4, uid, name, email, age);

    std::string uid;
    std::string name;
    std::string email;
    int age;
};
*/

class SqliteDatabase {
private:
    sqlite3* db;
    std::string db_path;
    std::string error;

public:
    explicit SqliteDatabase(const std::string& path) : db(nullptr), db_path(path) {
        int rc = sqlite3_open(db_path.c_str(), &db);
        if (rc) {
            error = sqlite3_errmsg(db);
            sqlite3_close(db);
        }
    }

    ~SqliteDatabase() {
        if (db) {
            sqlite3_close(db);
        }
    }

    SqliteDatabase(const SqliteDatabase&) = delete;
    SqliteDatabase& operator=(const SqliteDatabase&) = delete;

    sqlite3* get_connection() const {
        return db;
    }

    bool execute(const std::string& sql) {
        error.clear();
        char* errmsg = nullptr;
        int rc = sqlite3_exec(db, sql.c_str(), nullptr, nullptr, &errmsg);

        if (rc != SQLITE_OK) {
            error = errmsg ? errmsg : "未知错误";
            sqlite3_free(errmsg);
            return false;
        }else{
            sqlite3_free(errmsg);
            return true;
        }
    }

    sqlite3_stmt* prepare_statement(const std::string& sql) {
        error.clear();
        sqlite3_stmt* stmt = nullptr;
        int rc = sqlite3_prepare_v2(db, sql.c_str(), -1, &stmt, nullptr);

        if (rc != SQLITE_OK) {
            error = sqlite3_errmsg(db);
        }

        return stmt;
    }

    bool select(const std::string& sql, std::function<void(sqlite3_stmt*)> generator){
        sqlite3_stmt* stmt = prepare_statement(sql);
        if(!stmt) return false;

        int rc;
        while ((rc = sqlite3_step(stmt)) == SQLITE_ROW) {
            generator(stmt);
        }

        if (rc == SQLITE_DONE) {
            sqlite3_finalize(stmt);
            return true;
        }else{
            error = sqlite3_errmsg(db);
            sqlite3_finalize(stmt);
            return false;
        }
    }

    bool selectSelf(const std::string& sql, SqliteRowData& rd){
        sqlite3_stmt* stmt = prepare_statement(sql);
        if(!stmt) return false;

        int rc;
        if ((rc = sqlite3_step(stmt)) == SQLITE_ROW) {
            getRowData(stmt, rd);
            sqlite3_finalize(stmt);
            return true;
        }else{
            error = sqlite3_errmsg(db);
            sqlite3_finalize(stmt);
            return false;
        }
    }

    bool selectOne(const std::string& sql, int& data){
        sqlite3_stmt* stmt = prepare_statement(sql);
        if(!stmt) return false;

        int rc;
        if ((rc = sqlite3_step(stmt)) == SQLITE_ROW) {
            data = sqlite3_column_int(stmt, 0);
            sqlite3_finalize(stmt);
            return true;
        }else{
            error = sqlite3_errmsg(db);
            sqlite3_finalize(stmt);
            return false;
        }
    }

    inline int columnType(sqlite3_stmt *stmt, int col){
        return sqlite3_column_type(stmt, col);
    }

    inline std::string columnName(sqlite3_stmt *stmt, int col){
        return std::string(sqlite3_column_name(stmt, col));
    }

    void getColumnData(sqlite3_stmt *stmt, SqliteRowData& rd, size_t column){
        rd.__column_nulls[column] = true;
        switch (columnType(stmt, column)) {
        case SQLITE_INTEGER: {
            if(rd.__types.at(column) == 0){
                rd.__column_nulls[column] = false;
                int* ptr = reinterpret_cast<int*>(rd.__pointerDatas[column]);
                *ptr = sqlite3_column_int(stmt, column);
            }
        } break;
        case SQLITE_FLOAT: {
            if(rd.__types.at(column) == 1){
                rd.__column_nulls[column] = false;
                double* ptr = reinterpret_cast<double*>(rd.__pointerDatas[column]);
                *ptr = sqlite3_column_double(stmt, column);
            }
        } break;
        case SQLITE_TEXT: {
            if(rd.__types.at(column) == 2){
                rd.__column_nulls[column] = false;
                std::string* ptr = reinterpret_cast<std::string*>(rd.__pointerDatas[column]);
                *ptr = std::string(reinterpret_cast<const char*>(sqlite3_column_text(stmt, column)));
            }
        } break;
        case SQLITE_BLOB: {
#if 0
            const void *data = sqlite3_column_blob(stmt, column);
            int byteCount = sqlite3_column_bytes(stmt, column);
            const unsigned char *p = (const unsigned char *)data;
            for (int i = 0; i < byteCount && i < 10; i++) {
                printf("%02X ", p[i]);
            }
#else
            rd.__pointerDatas[column] = nullptr;
#endif
        } break;
        case SQLITE_NULL:
            break;
        default:
            break;
        }
    }

    void getRowData(sqlite3_stmt *stmt, SqliteRowData& rd){
        for(size_t column = 0; column < rd.__pointerDatas.size(); ++column){
            getColumnData(stmt, rd, column);
        }
    }
};

#define SqliteRowData_InitializeConstructor(Class, Count) \
    Class(): SqliteRowData( #Class , Count) { \
        SqliteRowData::__initializeConstructor(); \
    }
#define SqliteRowData_SetColumnPointerStart \
void __setColumnPointer(int column){ \
        switch(column){
#define SqliteRowData_SetColumnPointerEnd \
    default: break; \
    } \
}
#define SqliteRowData_SetPointer(column, field) \
__pointerDatas[column] = reinterpret_cast<void*>(&field); \
    __types[column] = typeInfo(field); \
    __fieldNames[column] = #field ;
#define SqliteRowData_SetColumnPointer1(f0) \
case 0: SqliteRowData_SetPointer(0, f0); break;
#define SqliteRowData_SetColumnPointer2(f0, f1) \
SqliteRowData_SetColumnPointer1(f0) \
    case 1: SqliteRowData_SetPointer(1, f1); break;
#define SqliteRowData_SetColumnPointer3(f0, f1, f2) \
SqliteRowData_SetColumnPointer2(f0, f1) \
    case 2: SqliteRowData_SetPointer(2, f2); break;
#define SqliteRowData_SetColumnPointer4(f0, f1, f2, f3) \
SqliteRowData_SetColumnPointer3(f0, f1, f2) \
    case 3: SqliteRowData_SetPointer(3, f3); break;
#define SqliteRowData_SetColumnPointer5(f0, f1, f2, f3, f4) \
SqliteRowData_SetColumnPointer4(f0, f1, f2, f3) \
    case 4: SqliteRowData_SetPointer(4, f4); break;
#define SqliteRowData_SetColumnPointer6(f0, f1, f2, f3, f4, f5) \
SqliteRowData_SetColumnPointer5(f0, f1, f2, f3, f4) \
    case 5: SqliteRowData_SetPointer(5, f5); break;
#define SqliteRowData_SetColumnPointer7(f0, f1, f2, f3, f4, f5, f6) \
SqliteRowData_SetColumnPointer6(f0, f1, f2, f3, f4, f5) \
    case 6: SqliteRowData_SetPointer(6, f6); break;
#define SqliteRowData_SetColumnPointer8(f0, f1, f2, f3, f4, f5, f6, f7) \
SqliteRowData_SetColumnPointer7(f0, f1, f2, f3, f4, f5, f6) \
    case 7: SqliteRowData_SetPointer(7, f7); break;
#define SqliteRowData_SetColumnPointer9(f0, f1, f2, f3, f4, f5, f6, f7, f8) \
SqliteRowData_SetColumnPointer8(f0, f1, f2, f3, f4, f5, f6, f7) \
    case 8: SqliteRowData_SetPointer(8, f8); break;
#define SqliteRowData_SetColumnPointer10(f0, f1, f2, f3, f4, f5, f6, f7, f8, f9) \
SqliteRowData_SetColumnPointer9(f0, f1, f2, f3, f4, f5, f6, f7, f8) \
    case 9: SqliteRowData_SetPointer(9, f9); break;
#define SqliteRowData_SetColumnPointer11(f0, f1, f2, f3, f4, f5, f6, f7, f8, f9, f10) \
SqliteRowData_SetColumnPointer10(f0, f1, f2, f3, f4, f5, f6, f7, f8, f9) \
    case 10: SqliteRowData_SetPointer(10, f10); break;
#define SqliteRowData_SetColumnPointerN(N, ...) SqliteRowData_SetColumnPointer##N (__VA_ARGS__)


#define InitializeSqliteRowData(Class, Count, ...) \
SqliteRowData_InitializeConstructor(Class, Count) \
    SqliteRowData_SetColumnPointerStart \
    SqliteRowData_SetColumnPointerN(Count, __VA_ARGS__) \
    SqliteRowData_SetColumnPointerEnd

#endif // SQLITE_DATABASE_H
